﻿using System.Data;
using DaoService.Entity;
using DaoService.Model;
using Dapper;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace DaoService.Services;

public interface IPlcInfoVariablesService
{
    Task<int> InsertAsync(PlcInfoVariableEntity model);
    Task<bool> UpdateAsync(PlcInfoVariableEntity model);
    Task<bool> DeleteAsync(int id);
    Task<PlcInfoVariableEntity> GetByIdAsync(int id);
    Task<IEnumerable<PlcInfoVariableEntity>> GetAllAsync();
    Task<IEnumerable<PlcInfoVariableEntity>> SearchAsync(string keyword);
    Task<int> SaveAsync(PlcInfoVariableEntity model);
}
public class PlcInfoVariablesService : IPlcInfoVariablesService
{
    private readonly IDbConnection _dbConnection;

    public PlcInfoVariablesService(IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public async Task<int> InsertAsync(PlcInfoVariableEntity model)
    {
        const string sql = @"
            INSERT INTO plc_info_variables (
                id,
                OrderNum, 
                Name, 
                CreatedDate, 
                ModifiedDate, 
                VariableAddress, 
                VariableName, 
                AccessType, 
                IntervalTime, 
                DataAcquisitionType, 
                PlcGroup
            ) 
            VALUES (
                @Id,
                @OrderNum, 
                @Name, 
                @CreatedDate, 
                @ModifiedDate, 
                @VariableAddress, 
                @VariableName, 
                @AccessType, 
                @IntervalTime, 
                @DataAcquisitionType, 
                @PlcGroup
            );
            SELECT LAST_INSERT_ID();";

        model.CreatedDate = DateTime.Now;
        model.ModifiedDate = DateTime.Now;

        return await _dbConnection.ExecuteScalarAsync<int>(sql, model);
    }

    public async Task<bool> UpdateAsync(PlcInfoVariableEntity model)
    {
        const string sql = @"
            UPDATE plc_info_variables 
            SET 
                OrderNum = @OrderNum,
                Name = @Name,
                ModifiedDate = @ModifiedDate,
                VariableAddress = @VariableAddress,
                VariableName = @VariableName,
                AccessType = @AccessType,
                IntervalTime = @IntervalTime,
                DataAcquisitionType = @DataAcquisitionType,
                PlcGroup = @PlcGroup
            WHERE Id = @Id";

        model.ModifiedDate = DateTime.Now;

        var affectedRows = await _dbConnection.ExecuteAsync(sql, model);
        return affectedRows > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        const string sql = "DELETE FROM plc_info_variables WHERE Id = @Id";
        var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Id = id });
        return affectedRows > 0;
    }

    public async Task<List<PlcInfoVariableEntity>> GetByNameAsync(string name)
    {
        const string sql = "SELECT * FROM plc_info_variables WHERE Name = @Name";
        return (List<PlcInfoVariableEntity>)await _dbConnection.QueryAsync<PlcInfoVariableEntity>(sql, name);
    }

    public async Task<PlcInfoVariableEntity> GetByIdAsync(int id)
    {
        const string sql = "SELECT * FROM plc_info_variables WHERE Id = @Id";
        return await _dbConnection.QueryFirstOrDefaultAsync<PlcInfoVariableEntity>(sql, new { Id = id });
    }

    public async Task<IEnumerable<PlcInfoVariableEntity>> GetAllAsync()
    {
        const string sql = "SELECT * FROM plc_info_variables ORDER BY OrderNum ASC";
        return await _dbConnection.QueryAsync<PlcInfoVariableEntity>(sql);
    }

    public async Task<IEnumerable<PlcInfoVariableEntity>> SearchAsync(string keyword)
    {
        const string sql = @"
            SELECT * 
            FROM plc_info_variables 
            WHERE 
                Name LIKE @Keyword OR 
                VariableName LIKE @Keyword OR 
                PlcGroup LIKE @Keyword
            ORDER BY OrderNum ASC";

        return await _dbConnection.QueryAsync<PlcInfoVariableEntity>(
            sql, 
            new { Keyword = $"%{keyword}%" });
    }

    public async Task<int> SaveAsync(PlcInfoVariableEntity model)
    {
        if (model.Id == 0)
        {
            return await InsertAsync(model);
        }
        else
        {
            var success = await UpdateAsync(model);
            return success ? model.Id : 0;
        }
    }
}